import os.path
import xlrd3
import re
import math

class Handler:
    _public_methods_ = ['open', 'close', 'getSheetsName', 'sheet']
    compilation = None

    def __init__(self):
        self.workbook = None

    def open(self, path):  #打开文件
        result = False
        if os.path.splitdrive(os.path.abspath(path))[0]==os.path.splitdrive(path)[0]:
            try:
                self.workbook = xlrd3.open_workbook(path, formatting_info=True)
                result = True
            except:
                pass 
        return result

    def close(self):
        self.workbook = None

    def getSheetsName(self):  #获取所有工作表名称
        if self.workbook is None:
            list = []
        else:
            list = self.workbook.sheet_names()
        return self.compilation.VARIANT(self.compilation.constants.VT_VARIANT | self.compilation.constants.VT_ARRAY, list)

    def sheet(self, index):  #打开工作表
        sheetHandle = None
        if not self.workbook is None:
            item = None
            try:
                if isinstance(index, int):
                    item = self.workbook.sheet_by_index(index)
                else:
                    item = self.workbook.sheet_by_name(index)
                sheetHandle = Sheet(item)
                sheetHandle.compilation = self.compilation
                sheetHandle.workbook = self.workbook
            except:
                pass 
        if sheetHandle is None:
            instance = self.compilation.VARIANT(self.compilation.constants.VT_DISPATCH, None)
        else:
            instance = self.compilation.wrap(sheetHandle)
        return instance

class Sheet:
    _public_methods_ = ['close', 'dataTranslation', 'setHDR', 'getHeader', 'toADO', 'toXML']

    def __init__(self, item):
        self.sheet = item
        self.__HDR = True  #第一行是否是标题
        self.__dataTranslation = DataTranslation()
        # self.__dataTranslation.loadPreset()
        self.__sheetHeader = []
        for colnum in range(0, self.sheet.ncols):
            self.__sheetHeader.append(self.__getColumnNumber(colnum))

    def close(self):
        self.__dataTranslation = None
        self.sheet = None

    def __getColumnNumber(self, serial):
        sequence = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
        collect = []
        if serial > 25:
            while True:
                d = int( serial / 26 )
                remainder = serial % 26
                if d <= 25:
                    collect.insert( 0, sequence[remainder] )
                    collect.insert( 0, sequence[d - 1] )
                    break
                else:
                    collect.insert( 0, sequence[remainder] )
                    serial = d - 1
        else:
            collect.append( sequence[serial] )
        return "".join( collect )

    def dataTranslation(self):
        return self.compilation.wrap(self.__dataTranslation)

    def setHDR(self, value):
        if isinstance(value, bool):
            self.__HDR = value

    def getHeader(self):  #获取工作表表头
        if self.__HDR:
            result = self.sheet.row_values(0)
        else:
            result = self.__sheetHeader
        return self.compilation.VARIANT(self.compilation.constants.VT_VARIANT | self.compilation.constants.VT_ARRAY, result)

    def toADO(self):
        container = None
        if self.sheet.nrows > 0:
            container = self.compilation.CreateObject(r'ADODB.Recordset')
            if self.__HDR:
                fields = self.sheet.row_values(0)
                startRow = 1
            else:
                fields = self.__sheetHeader
                startRow = 0

            # ——————————————————————————————————————————
            # 添加字段默认使用字符串值，参数参考关键字：DataTypeEnum。
            # https://docs.microsoft.com/zh-cn/office/client-developer/access/desktop-database-reference/datatypeenum
            # ——————————————————————————————————————————
            for field in fields:
                container.Fields.Append(field, 200, 255)

            container.Open()
            for rownum in range(startRow, self.sheet.nrows):
                container.AddNew()
                for colnum in range(0, self.sheet.ncols):
                    cell = self.sheet.cell(rownum, colnum)
                    fmt = self.workbook.format_map[self.workbook.xf_list[cell.xf_index].format_key]
                    container.Fields.Item(fields[colnum]).Value = self.__dataTranslation.processor(fmt, cell.value)
                container.Update()
            container.MoveFirst()
        
        if container is None:
            result = self.compilation.VARIANT(self.compilation.constants.VT_DISPATCH, None)
        else:
            result = container
        return result

    def toXML(self):
        container = None
        if self.sheet.nrows > 0:
            container = self.compilation.CreateObject(r'Msxml2.FreeThreadedDOMDocument.6.0')
            root = container.createElement("sheet")
            container.appendChild(root)
            schema = container.createElement("Schema")
            root.appendChild(schema)
            if self.__HDR:
                fields = self.sheet.row_values(0)
                startRow = 1
            else:
                fields = self.__sheetHeader
                startRow = 0
            for fi in range(len(fields)):
                fieldNode = container.createElement("field")
                schema.appendChild(fieldNode)
                fieldNode.setAttribute("order", fi+1)
                fieldNode.setAttribute("name", fields[fi])
            data = container.createElement("data")
            root.appendChild(data)
            for rownum in range(startRow, self.sheet.nrows):
                rowElement = container.createElement("row")
                data.appendChild(rowElement)
                rowElement.setAttribute("id", rownum+1)
                for colnum in range(0, self.sheet.ncols):
                    cell = self.sheet.cell(rownum, colnum)
                    fmt = self.workbook.format_map[self.workbook.xf_list[cell.xf_index].format_key]
                    cellElement = container.createElement("cell")
                    rowElement.appendChild(cellElement)
                    cellElement.setAttribute("name", fields[colnum])
                    cellElement.text = self.__dataTranslation.processor(fmt, cell.value)

        if container is None:
            result = self.compilation.VARIANT(self.compilation.constants.VT_DISPATCH, None)
        else:
            result = container
        return result

class DataTranslation:
    _public_methods_ = ['loadPreset', 'addRule']

    def __init__(self): 
        self.__comparisons = []
        self.__appropriate = []
        self.__converters = []

        self.Language_CHS = {}
        self.Language_CHS['Weekday'] = ['一', '二', '三', '四', '五', '六', '日']

    def addRule(self, pattern, types, func): 
        self.__comparisons.append(pattern)
        self.__appropriate.append(types)
        self.__converters.append(func)

    def processor(self, fmt, rawData):
        formatStyle = fmt.format_str
        value = rawData

        def compile(matched):
            select = matched.group(1)
            if select is None:
                put = matched.group()
                match = re.match(r'\"(.+)\"', put)
                if not match is None:
                    put = match.group(1)
                collector[collector[0]] = collector[collector[0]] + str(put)
            else:
                while len(select)>0:
                    put = None
                    for index in range(len(self.__comparisons)):
                        if fmt.type in self.__appropriate[index]:
                            match = re.compile(self.__comparisons[index]).match(select)
                            if not match is None:
                                put = self.__converters[index](match, fmt, value, buffer)
                                select = select[len(match.group()):]
                                break
                    if put is None:
                        put = select[0]
                        if put == ';':
                            put = ''
                            collector.append('')
                            collector[0] = len(collector)-1
                            buffer['section'] = collector[0]
                            for item in ['language', 'DBNum']:
                                buffer[item] = None
                        select = select[1:]
                    collector[collector[0]] = collector[collector[0]] + str(put)

        if (formatStyle is None) or (formatStyle == 'General'):
            result = rawData
            if isinstance(result, float):
                if (result % 1 == 0.0) : result = int(result)
        else:
            buffer = {}
            buffer['section'] = 1
            buffer['language'] = None
            buffer['DBNum'] = None
            buffer['purelyFormatCode'] = self.__filterStyleCharacters(formatStyle).split(';')
            if fmt.type == 1:
                value = xlrd3.xldate_as_datetime(value, 0)
            if len(self.__comparisons) > 0:
                collector = [1,'']
                re.sub(r'\"[^\"]*\"|([^\"]+)', compile, formatStyle)
                collector.pop(0)
                for i in range(len(collector)):
                    collector[i] = re.sub(r'\\(.){1}', lambda matched:matched.group(1), collector[i])
            else:
                collector = [rawData]
            result = collector
            resultNum = len(result)
            if fmt.type == 3:
                if isinstance(rawData, (float, int)) and resultNum > 1:
                    if rawData > 0:
                        result = result[0]
                    elif value < 0:
                        result = result[1]
                    else:
                        if resultNum < 3:
                            result = result[0]
                        else:
                            result = result[2]
            if isinstance(result, list):
                result = result[0]
        return result

    def __filterStyleCharacters(self, style):
        result = ''
        def replace(matched):
            put = matched.group()
            if matched.group(1) is None:
                put = ''
            return put
        if not style is None:
            result = re.sub(r'\"[^\"]*\"|([^\"]+)', replace, style)
        return result

    def loadPreset(self):  #加载预设格式化规则
        def process(matched, fmt, value, buffer):
            if matched.group() == '[$-804]':
                buffer['language'] = 'zh-CN'
            elif matched.group() == '[DBNum1]':
                buffer['DBNum'] = 1
            elif matched.group() == '[DBNum2]':
                buffer['DBNum'] = 2
            return ''
        self.addRule('\[.+?\]', [1,3,4], process)

        def process(matched, fmt, value, buffer):
            if isinstance(value, float):
                if (value % 1 == 0.0) : value = int(value)
            return str(value)
        self.addRule('@', [1,3,4], process)

        def process(matched, fmt, value, buffer):
            result = value
            if isinstance(value, (int, float)):
                style = matched.group()
                negative = False
                if style[len(style)-2] == '_':
                    style = style[0:len(style)-2]
                style = style.split('.')

                if value < 0:
                    negative = True
                    value = math.fabs(value)

                if len(style) == 1 and isinstance(value, float):
                    value = int(round(value, 0))
                value = str(value).split('.')

                value[0] = value[0].rjust(len(style[0]), '0')
                if len(style)>1:
                    if len(value)==1:
                        value.append('0')
                    if len(style[1]) > len(value[1]):
                        value[1] = value[1].ljust(len(style[1]), '0')
                    else:
                        value[1] = str(int(round(float(value[1])/(10**(len(value[1])-len(style[1]))), 0)))
                result = '.'.join(value)
                if negative:
                    result = '-' + result
            return result
        self.addRule('[0]+(\.[0]+)*(_[ )]){0,1}', [3,4], process)

        def process(matched, fmt, value, buffer):
            if not('timeArgsArea' in buffer):
                buffer['timeArgsArea'] = None
            if buffer['timeArgsArea'] != buffer['section']:
                buffer['timeArgsArea'] = buffer['section']
                buffer['hourTo12'] = None
                buffer['toMinute'] = None
                if buffer['purelyFormatCode'][buffer['timeArgsArea']-1].find('AM/PM') > -1:
                    buffer['hourTo12'] = True

            style = matched.group()
            if style=='yyyy':
                result = str(value.year)
            elif style=='yy':
                result = str(value.year)[2:4]
            elif style=='dd':
                result = str(value.day).rjust(2, '0')
            elif style=='d':
                result = str(value.day)
            elif style=='AM/PM':
                result = value.strftime('%p')
            elif style=='hh':
                buffer['toMinute'] = True
                if buffer['hourTo12']:
                    result = value.strftime('%I').rjust(2, '0')
                else:
                    result = value.strftime('%H').rjust(2, '0')
            elif style=='h':
                buffer['toMinute'] = True
                if buffer['hourTo12']:
                    result = str(int(value.strftime('%I')))
                else:
                    result = str(int(value.strftime('%H')))
            elif style=='ss':
                result = str(value.second).rjust(2, '0')
            elif style=='s':
                result = str(value.second)
            elif style=='mm':
                if buffer['toMinute']:
                    result = str(value.minute).rjust(2, '0')
                else:
                    result = str(value.month).rjust(2, '0')
            elif style=='m':
                if buffer['toMinute']:
                    result = str(value.minute)
                else:
                    result = str(value.month)
            elif style=='mmmm':
                result = value.strftime('%B')
            elif style=='mmm':
                result = value.strftime('%b')
            elif style=='dddd':
                if buffer['language'] == 'zh-CN':
                    result = '星期' + self.Language_CHS['Weekday'][value.weekday()]
                else:
                    result = value.strftime('%A')
            elif style=='ddd':
                if buffer['language'] == 'zh-CN':
                    result = '周' + self.Language_CHS['Weekday'][value.weekday()]
                else:
                    result = value.strftime('%a')
            return result
        self.addRule('m{1,5}|d{1,4}|y{4}|y{2}|h{1,2}|s{1,2}|AM/PM', [1], process)